from flask import *
from pymysql import *

Book_Bp = Blueprint(name="book",import_name = __name__)
db_connect = Connect( user="root",password="123456", database="bookms_hw_test" )

def seeAll():
    cursor = db_connect.cursor()
    sql_str_list = '''select * from  BookMS  '''
    cursor.execute(sql_str_list)
    data = cursor.fetchall()
    print(data)
    cursor.close()
    book_list = []
    if len(data) != 0:

        for item in data:
            book = {}
            book["bid"] = item[0]
            book["name"] = item[1]
            book["price"] = item[2]
            book["summary"] = item[3]
            book_list.append(book)

        print(book_list)
        return book_list

    else:
        return []

        #return book_list
@Book_Bp.route("/list",methods=["GET","POST"])
def list():
    if request.method =="GET":
        return render_template("list.html")
    else:
       return seeAll()

#添加学员，如果是GEI请求便返回添加学员的添加页面ADD.HTML如果是POST请求就返回当前数据库所有的书籍记录
@Book_Bp.route("/add",methods=["GET","POST"])
def add_book():
    if request.method =="GET":
        return render_template("add.html")

    else:
        cursor = db_connect.cursor()
        name = request.values.get("name")

        price = float(request.values.get("price"))  # 将价格从字符串转换为浮点数
        summary = request.values.get("summary")
        sql_str_add =f'''insert into BookMS (name,price,summary) values(%s,{price},%s)'''

        print(name,price,summary)
        cursor.execute(sql_str_add,[name,summary])
        db_connect.commit()
        cursor.close()
        return seeAll()

#如果是get请求，便就返回指定的ID的书籍的原信息
#如果是post请求，就进行修改指定id的书籍，然后将修改后的信息的书籍列表全部输出
@Book_Bp.route("/update/<bid>",methods=["GET","POST"])
def update(bid):
    if request.method =="GET":
        sql_str_select = f'''select * from  BookMS where bid ={bid} '''
        cursor = db_connect.cursor()
        cursor.execute(sql_str_select)

        data = cursor.fetchone()
        book = {}
        book["bid"] = data[0]
        book["name"] = data[1]
        book["price"] = data[2]
        book["summary"] = data[3]
        #book_json = jsonify(book)
       # book=json.dumps(book, ensure_ascii=False)
        print(book)
       # return render_template("update.html",book = book)
        return book
    else:
        cursor = db_connect.cursor()
        name = request.values.get("name")
        price = float(request.values.get("price"))  # 将价格从字符串转换为浮点数
        summary = request.values.get("summary")
        sql_str_update = f'''update BookMS set name =%s,price ={price},summary=%s where bid=%s'''
        cursor.execute(sql_str_update,[name,summary,bid])
        db_connect.commit()
        cursor.close()
        return seeAll()
#get请求输出删除指定id书籍后的书籍列表
@Book_Bp.route("/delete/<bid>",methods = ["POST","GET"])
def delete(bid):
    if request.method=="GET":
        cursor = db_connect.cursor()
        sql_str_delete =f'''DELETE FROM BookMS where bid ={bid}'''
        cursor.execute(sql_str_delete)
        db_connect.commit()
        return seeAll()
    else:
        pass

#发送GET请求来进行模糊查询，返回查询结果
@Book_Bp.route("/search/<str>",methods=["GET"])
def search(str):
    sql_str_search = f'''select * from BookMS where name like %s or summary like %s'''
    cursor = db_connect.cursor()
    cursor.execute(sql_str_search, [f'%{str}%', f'%{str}%'])

    data = cursor.fetchall()
    book_list=[]

    for item in data:
        book= {}
        book["bid"] = item[0]
        book["name"] = item[1]
        book["price"] = item[2]
        book["summary"] = item[3]
        book_list.append(book)

    # book_list_json = jsonify(book_list)
    return book_list